package com.example.fyptest3.sqlite.helper;

import java.util.ArrayList;
import java.util.List;

import com.example.fyptest3.sqlite.model.Category;
import com.example.fyptest3.sqlite.model.Task;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper{

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "AssignmentMenu";
	
	//table names
	private static final String TABLE_CATEGORY = "categories";
	private static final String TABLE_TASK = "tasks";
//	private static final String TABLE_CATEGORY_TASK = "category_task";
	
	//table notes
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_INFO = "info";
	private static final String KEY_IMAGE_REF = "image_ref";
//	private static final String KEY_CATEGORY_ID = "category_id";
//	private static final String KEY_TASK_ID = "task_id";
	
	
	//STATEMENTS
	private static final String CREATE_TABLE_CATEGORY = "CREATE TABLE "+TABLE_CATEGORY+"("
			+KEY_ID+" INTEGER PRIMARY KEY,"+KEY_NAME+" TEXT"+")";
	private static final String CREATE_TABLE_TASK = "CREATE TABLE "+TABLE_TASK+"("
			+KEY_ID+" INTEGER PRIMARY KEY,"+KEY_NAME+" TEXT,"+KEY_INFO+" TEXT,"
			+KEY_IMAGE_REF+" TEXT"+")";
	//private static final String CREATE_TABLE_CATEGORY_TASK = "CREATE TABLE "+TABLE_CATEGORY_TASK+"("
	//		+KEY_ID+" INTEGER PRIMARY KEY,"+KEY_CATEGORY_ID+" INTEGER,"+KEY_TASK_ID+" INTEGER"+")";
	
	//constructor
	public DataBaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_TABLE_CATEGORY);
		db.execSQL(CREATE_TABLE_TASK);
	//	db.execSQL(CREATE_TABLE_CATEGORY_TASK);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_CATEGORY);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_TASK);
	//	db.execSQL("DROP TABLE IF EXISTS "+TABLE_CATEGORY_TASK);
		
		onCreate(db);
	}
	
	//same as INSERT CATEGORY in category table
	public long createCategory(Category c){
		//, long[] task_ids
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, c.getName());
		
		long category_id = db.insert(TABLE_CATEGORY, null, values);
		
//		for(long task_id : task_ids){
//			createCategoryTask(category_id, task_id);
//		}
		
		return category_id;
	}
	
	//same as SELECT * FROM CATEGORY TABLE
	public List<Category> getAllCategories() {
	    List<Category> categories = new ArrayList<Category>();
	    String selectQuery = "SELECT  * FROM " + TABLE_CATEGORY;
	 
	//    Log.e(LOG, selectQuery);
	 
	    SQLiteDatabase db = this.getReadableDatabase();
	    Cursor cur = db.rawQuery(selectQuery, null);
	 
	    // looping through all rows and adding to list
	    if (cur.moveToFirst()) {
	        do {
	            Category cat = new Category();
	            cat.setId(cur.getInt((cur.getColumnIndex(KEY_ID))));
	            cat.setName((cur.getString(cur.getColumnIndex(KEY_NAME))));
	            
	            categories.add(cat);
	        } while (cur.moveToNext());
	    }
	 
	    return categories;
	}
	
	
	//same as INSERT task in tasks table
	public long createTask(Task task) {
	    SQLiteDatabase db = this.getWritableDatabase();
	 
	    ContentValues values = new ContentValues();
	    values.put(KEY_NAME, task.getName());
	    values.put(KEY_INFO, task.getInfo());
	    values.put(KEY_IMAGE_REF, task.getImageRef());
	 
	    // insert row
	    long task_id = db.insert(TABLE_TASK, null, values);
	 
	    return task_id;
	}
	
	
	//same as SELECT * FROM task table
	public List<Task> getAllTasks() {
	    List<Task> task_list = new ArrayList<Task>();
	    String selectQuery = "SELECT  * FROM " + TABLE_TASK;
	 
	//    Log.e(LOG, selectQuery);
	 
	    SQLiteDatabase db = this.getReadableDatabase();
	    Cursor cur = db.rawQuery(selectQuery, null);
	 
	    // looping through all rows and adding to list
	    if (cur.moveToFirst()) {
	        do {
	            Task t = new Task();
	            t.setId(cur.getInt((cur.getColumnIndex(KEY_ID))));
	            t.setName(cur.getString(cur.getColumnIndex(KEY_NAME)));
	            String s = cur.getString(cur.getColumnIndex(KEY_IMAGE_REF));
	            if(s==null)
	            	s="";
	            t.setImageRef(s);
	 
	            // adding to tags list
	            task_list.add(t);
	        } while (cur.moveToNext());
	    }
	    return task_list;
	}
	
	//update task in db
	public int updateTask(Task task) {
	    SQLiteDatabase db = this.getWritableDatabase();
	 
	    ContentValues values = new ContentValues();
	    values.put(KEY_NAME, task.getName());
	    values.put(KEY_IMAGE_REF, task.getImageRef());
	 
	    // updating row
	    return db.update(TABLE_TASK, values, KEY_ID+"="+task.getId(),null);
	}
	
	
	// closing database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }
    
	
	
//END	
}
